//
// Created by zlf on 20-1-7.
//
#include <iostream>
#include <QCoreApplication>
#include <QDebug>


#include <odb/database.hxx>
#include <odb/transaction.hxx>
#include <string>
#include <memory>   // std::auto_ptr
#include <cstdlib>  // std::exit
#include <iostream>

#include <odb/database.hxx>

#if defined(DATABASE_MYSQL)

#include <odb/mysql/database.hxx>

#elif defined(DATABASE_SQLITE)
#  include <odb/connection.hxx>
#  include <odb/transaction.hxx>
#  include <odb/schema-catalog.hxx>
#  include <odb/sqlite/database.hxx>
#elif defined(DATABASE_PGSQL)
#  include <odb/pgsql/database.hxx>
#elif defined(DATABASE_ORACLE)
#  include <odb/oracle/database.hxx>
#elif defined(DATABASE_MSSQL)
#  include <odb/mssql/database.hxx>
#else
#  error unknown database; did you forget to define the DATABASE_* macros?
#endif

#include "notetree.h"
#include "notetree-odb.hxx"

using odb::database;
using odb::transaction;
using namespace std;


static char *user = "root";
static char *userpasswd = "201314";
static char *database = "odb";

void testInsert() {
//	try {
//		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));
//
//		NoteTree noteTree1 = NoteTree();
//		transaction t(db->begin());
//		db->persist(noteTree1);
//		t.commit();
//	} catch (const odb::exception& e) {
//		qDebug() << e.what() << endl;
//	}

//	try {
//		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));
//		NoteTree noteTree1 = NoteTree("zlssf");
//		transaction t(db->begin());
//		db->persist(noteTree1);
//		t.commit();
//	} catch (const odb::exception& e) {
//		qDebug() << e.what() << endl;
//	}

	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		NoteTree noteTree1 = NoteTree(0, "alssf");
		transaction t(db->begin());
		db->persist(noteTree1);
		qDebug() << "insert id:" << noteTree1.id();
		t.commit();
	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}
}


void testUpdate() {
	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		QSharedPointer<NoteTree> note(db->query_one<NoteTree>(query::id == 1));

		qDebug() << note.data()->name();
		note.data()->setName("dd");//修改数据
		db->update(note);

		t.commit();

		qDebug() << note.operator*().name();
	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}

	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());
//		typedef odb::query<NoteTree> query;
		NoteTree *noteTree = new NoteTree(2, 1, "zlf12122");

		QSharedPointer<NoteTree> note(noteTree);

		qDebug() << note.data()->name();
		db->update(note);

		t.commit();

		qDebug() << note.operator*().name();
	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
		NoteTree note1(2, 1, "zlf12122");
	}
}

void testDelete() {
	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		QSharedPointer<NoteTree> note(db->query_one<NoteTree>(query::id == 1));

		qDebug() << note.data()->name();
		note.data()->setName("dd");//修改数据
		db->erase(note);

		t.commit();

		qDebug() << note.operator*().name();
	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}

	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		db->erase_query<NoteTree>(query::id == 1);

		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}
}

void testQuery() {
	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		typedef odb::result<NoteTree> result;

		result r(db->query<NoteTree>(query::true_expr));

		for (result::iterator i(r.begin()); i != r.end(); ++i)
			cout << i.id() << endl;
		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}

	qDebug() << "----------------------";

	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		typedef odb::result<NoteTree> result;
		//没有条件，将所有数据排序
		result r(db->query<NoteTree>(query::true_expr + " ORDER BY" + query::id + "desc", true));

		for (result::iterator i(r.begin()); i != r.end(); ++i)
			cout << i.id() << endl;
		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}

	qDebug() << "----------------------";

	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());
		typedef odb::query<NoteTree> query;
		typedef odb::result<NoteTree> result;
		//有条件，将所有数据排序.也可以进行分页
		query b(query::type == 1);
		result r(db->query<NoteTree>(query(b) + " ORDER BY" + query::id + "desc", true));

		for (result::iterator i(r.begin()); i != r.end(); ++i)
			cout << i.id() << endl;
		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}
}

void testExecute() {
	qDebug() << "----------------------";
	typedef odb::query<NoteTree> query;
	QString sql = QString("SELECT COUNT(id) FROM %1 WHERE %2 = %3 AND `type` = 1")
			.arg(query::parent_id.table())
			.arg(query::parent_id.column())
			.arg(0);
	qDebug() << sql;

	try {
		auto_ptr<odb::database> db(new odb::mysql::database(user, userpasswd, database));

		transaction t(db->begin());

		int i = db->execute(sql.toStdString());

		qDebug() << "testExecute:" << i;
		t.commit();

	} catch (const odb::exception& e) {
		qDebug() << e.what() << endl;
	}
}

int main() {
//	testInsert();
//	testUpdate();
//	testDelete();
	testQuery();
//	testExecute();

	qDebug() << "end";
	return 0;
}